#!/usr/bin/env node

const Database = require('better-sqlite3');
const db = new Database('./data/delivery-management.db');

console.log('🔍 检查参数表结构和数据映射...\n');

// 1. 检查现有的元数据表
const tables = db.prepare(`
  SELECT name FROM sqlite_master 
  WHERE type='table' AND name LIKE '%metadata%'
`).all();

console.log('元数据表:', tables.map(t => t.name));

// 2. 检查table_c2的结构
console.log('\n检查命名表 table_c2 结构:');
const c2Columns = db.prepare(`PRAGMA table_info(table_c2)`).all();
console.log('字段列表:');
c2Columns.forEach(col => {
  console.log(`  ${col.name} (${col.type})`);
});

// 3. 检查实例表的结构
console.log('\n检查实例表 device_instances_instrumentation_controller 结构:');
const instanceColumns = db.prepare(`PRAGMA table_info(device_instances_instrumentation_controller)`).all();
console.log('字段列表:');
instanceColumns.forEach(col => {
  console.log(`  ${col.name} (${col.type})`);
});

// 4. 检查实例表和参数表的关联
console.log('\n检查实例表和参数表的字段对应关系:');
const instanceFieldsPattern = instanceColumns
  .filter(col => col.name.startsWith('naming_') || col.name.startsWith('position_') || col.name.startsWith('base_params_'))
  .map(col => col.name);

console.log('实例表中的参数字段:');
instanceFieldsPattern.forEach(field => {
  console.log(`  ${field}`);
});

// 5. 测试有数据的实例表
console.log('\n检查有数据的实例表:');
const pipingTable = 'device_instances_piping_cooling_water_spray_system';
const pipingInstance = db.prepare(`SELECT * FROM [${pipingTable}] LIMIT 1`).get();

if (pipingInstance) {
  console.log('\n配管专业实例数据示例:');
  console.log(`设备类型代码: ${pipingInstance.device_type_code}`);
  console.log(`实例编号: ${pipingInstance.instance_number}`);
  console.log(`对象编号: ${pipingInstance.object_number}`);
  
  // 显示参数字段值
  const paramFields = Object.keys(pipingInstance).filter(key => 
    key.startsWith('naming_') || key.startsWith('position_') || key.startsWith('base_params_')
  );
  
  console.log('\n参数字段数据:');
  paramFields.forEach(field => {
    if (pipingInstance[field] !== null && pipingInstance[field] !== '') {
      console.log(`  ${field}: ${pipingInstance[field]}`);
    }
  });
}

// 6. 检查前端API的参数获取逻辑
console.log('\n6. 检查前端API参数获取的映射逻辑:');

// 模拟device-hierarchy-by-name API的逻辑
const testDeviceCode = '仪表-0015';
const deviceWithMapping = db.prepare(`
  SELECT 
    d.device_id,
    d.name,
    d.classification_name,
    d.classification_code,
    d.specialty,
    dtm.naming_table,
    dtm.position_table,
    dtm.base_params_table,
    dtm.english_name
  FROM devices d
  LEFT JOIN device_type_mappings dtm ON d.classification_code = dtm.classification_code AND d.specialty = dtm.specialty
  WHERE d.device_id = ?
`).get(testDeviceCode);

console.log('\n设备及其参数表映射:');
console.log(`设备ID: ${deviceWithMapping.device_id}`);
console.log(`设备名称: ${deviceWithMapping.name}`);
console.log(`对象类: ${deviceWithMapping.classification_name}`);
console.log(`专业: ${deviceWithMapping.specialty}`);
console.log(`英文名: ${deviceWithMapping.english_name}`);
console.log(`命名表: ${deviceWithMapping.naming_table}`);
console.log(`位置表: ${deviceWithMapping.position_table}`);
console.log(`基础参数表: ${deviceWithMapping.base_params_table}`);

// 7. 检查实例表字段是否与参数表匹配
console.log('\n7. 验证实例表字段与参数表的匹配关系:');

if (deviceWithMapping.naming_table) {
  // 检查命名表字段
  const namingTableColumns = db.prepare(`PRAGMA table_info(${deviceWithMapping.naming_table})`).all();
  console.log(`\n命名表 ${deviceWithMapping.naming_table} 字段:`);
  namingTableColumns.forEach(col => {
    if (col.name !== 'id') {
      const instanceFieldName = `naming_${col.name}`;
      const hasField = instanceFieldsPattern.includes(instanceFieldName);
      console.log(`  ${col.name} -> ${instanceFieldName} ${hasField ? '✓' : '✗'}`);
    }
  });
}

if (deviceWithMapping.position_table) {
  // 检查位置表字段
  const positionTableColumns = db.prepare(`PRAGMA table_info(${deviceWithMapping.position_table})`).all();
  console.log(`\n位置表 ${deviceWithMapping.position_table} 字段:`);
  positionTableColumns.forEach(col => {
    if (col.name !== 'id') {
      const instanceFieldName = `position_${col.name}`;
      const hasField = instanceFieldsPattern.includes(instanceFieldName);
      console.log(`  ${col.name} -> ${instanceFieldName} ${hasField ? '✓' : '✗'}`);
    }
  });
}

if (deviceWithMapping.base_params_table) {
  // 检查基础参数表字段
  const baseParamsTableColumns = db.prepare(`PRAGMA table_info(${deviceWithMapping.base_params_table})`).all();
  console.log(`\n基础参数表 ${deviceWithMapping.base_params_table} 字段:`);
  baseParamsTableColumns.forEach(col => {
    if (col.name !== 'id') {
      const instanceFieldName = `base_params_${col.name}`;
      const hasField = instanceFieldsPattern.includes(instanceFieldName);
      console.log(`  ${col.name} -> ${instanceFieldName} ${hasField ? '✓' : '✗'}`);
    }
  });
}

console.log('\n✅ 参数映射检查完成');

db.close();